package com.pic.mgt.dao.syushin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.dao.BusinessMgtDao;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;

public final class SyushinBusinessMgtDao extends BusinessMgtDao
						implements ISyushinBusinessMgtDao {
	// 資料表名稱
	private final String tableName = "syushin_business_mgt";
	
	private final String insertBasicSqlScript;
	private final String findTargetFileByBucketFilenameSqlScript;
	private final String updateStatusAndBucketFilenameByUnitNameSqlScript;
	private final String findSyushinBusinessMgtInfoByUnitNameSqlScript;
	
	public SyushinBusinessMgtDao() {
		super();
		
		// 建立 Insert Basic 的 Sql script
		insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立  find target file by bucket filename 的 Sql script
		findTargetFileByBucketFilenameSqlScript = createFindTargetFileByBucketFilenameSqlScript();
		
		// 建立 find syushin business mgt info by unit name 的 Sql script
		this.findSyushinBusinessMgtInfoByUnitNameSqlScript = createFindSyushinBusinessMgtInfoByUnitNameSqlScriptt();
		
		// 建立 update status and bucket filename by unit name 的 sql script
		this.updateStatusAndBucketFilenameByUnitNameSqlScript = createUpdateStatusAndBucketFilenameByUnitNameSqlScript();
	}
	
	private String createFindSyushinBusinessMgtInfoByUnitNameSqlScriptt() {
		Vector<String> selectFieldsVector =
									new Vector<String>();

		selectFieldsVector.add(_bucketFilename);
		selectFieldsVector.add(_confirmationFile);
		selectFieldsVector.add(_status);
		selectFieldsVector.add(_targetFile);
		selectFieldsVector.add(_targetFilesize);
		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_type);
		selectFieldsVector.add(_unitName);
		selectFieldsVector.add(_uploadingTime);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _unitName);
	}
	
	private String createUpdateStatusAndBucketFilenameByUnitNameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_status);
		updateFieldsVector.add(_bucketFilename);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_unitName);
	}
	
	private String createFindTargetFileByBucketFilenameSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_targetFile);

		return
		SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _bucketFilename);
	}
	
	private String createInsertBasicSQLScript() {
		Vector<String> insertFieldsVector =
							new Vector<String>();
		
		// 要新增的欄位
		insertFieldsVector.add(SyushinBusinessMgtDao._confirmationFile);
		insertFieldsVector.add(SyushinBusinessMgtDao._targetFile);
		insertFieldsVector.add(SyushinBusinessMgtDao._type);
		insertFieldsVector.add(SyushinBusinessMgtDao._targetFilesize);
		insertFieldsVector.add(SyushinBusinessMgtDao._status);
		insertFieldsVector.add(SyushinBusinessMgtDao._unitName);
		insertFieldsVector.add(SyushinBusinessMgtDao._terminalId);
		insertFieldsVector.add(SyushinBusinessMgtDao._uploadingTime);
		
		return SqlScriptUtils.createNamedParamInsertScript(this.tableName,
											     insertFieldsVector);
	}
	
	@Override
	public int updateStatusAndBucketFilenameByUnitName(String status,
			String bucketFilename, String unitName) throws DataAccessException {
		if (null != status && null != bucketFilename && null != unitName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_status, status,
									  _bucketFilename, bucketFilename,
									  "__ConditionValue", unitName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateStatusAndBucketFilenameByUnitNameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public SyushinBusinessMgtInfoBean findSyushinBusinessMgtInfoByUnitName(
			String unitName) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findSyushinBusinessMgtInfoByUnitNameSqlScript,
					Collections.singletonMap((String)_unitName, (Object)unitName),
					new RowMapper<SyushinBusinessMgtInfoBean>() {
						public SyushinBusinessMgtInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							SyushinBusinessMgtInfoBean syushinBusinessMgtInfoBean = new SyushinBusinessMgtInfoBean();
						
							syushinBusinessMgtInfoBean.setBucketFilename(rs.getString(_bucketFilename));
							syushinBusinessMgtInfoBean.setConfirmationFile(rs.getString(_confirmationFile));
							syushinBusinessMgtInfoBean.setStatus(rs.getString(_status));
							syushinBusinessMgtInfoBean.setTargetFile(rs.getString(_targetFile));
							syushinBusinessMgtInfoBean.setTargetFilesize(rs.getLong(_targetFilesize));
							syushinBusinessMgtInfoBean.setTerminalId(rs.getString(_terminalId));
							syushinBusinessMgtInfoBean.setType(rs.getString(_type));
							syushinBusinessMgtInfoBean.setUnitName(rs.getString(_unitName));
							syushinBusinessMgtInfoBean.setUploadingTime(new Date(rs.getTimestamp(_uploadingTime).getTime()));
							syushinBusinessMgtInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							return syushinBusinessMgtInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public List<String> findTargetFileByBucketFilename(String bucketFilename)
			throws DataAccessException {
		return
		super.namedParameterJdbcTemplate.query(
					this.findTargetFileByBucketFilenameSqlScript,
					Collections.singletonMap((String)_bucketFilename, (Object)bucketFilename),
					new RowMapper<String>() {
						public String mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString(_targetFile);
						}
					  }
					);
	}
	
	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			return
				super.namedParameterJdbcTemplate.update(this.insertBasicSqlScript,
														insertValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public String getTableName() {
		return this.tableName;
	}
}
